# -*- coding: utf-8 -*-
import logging
from datetime import datetime
from typing import Any

from sqlalchemy import Column, Integer, DateTime, inspect, MetaData
from sqlalchemy.ext.declarative import declarative_base, as_declarative, declared_attr
from sqlalchemy.orm import scoped_session, sessionmaker, Query, Session

from py_engine import engine, session

logger = logging.getLogger(__name__)

class CaseInsensitiveBase:
    @declared_attr
    def __tablename__(cls):
        # 假设表名是类名的小写形式（可选）
        return cls.__name__.lower()

    # 自动将数据库列名（如 'UserName'）映射到模型属性名（如 'username'）
    def __init_subclass__(cls, **kwargs):
        super().__init_subclass__(**kwargs)
        # 遍历类的所有属性，找到 Column 并调整 name
        for attr_name, attr_value in cls.__dict__.items():
            if isinstance(attr_value, Column):
                # 如果 Column 没有显式指定 name，则自动转换
                if not attr_value.name:
                    # 假设数据库列名是属性名的驼峰形式（如 'username' -> 'UserName'）
                    # 这里可以根据你的命名规则调整
                    attr_value.name = attr_name.title()  # 简单示例：首字母大写

#
# metadata=MetaData(naming_convention={
#     "ix": "ix_%(column_0_label)s",
#     "uq": "uq_%(table_name)s_%(column_0_name)s",
#     "ck": "ck_%(table_name)s_%(constraint_name)s",
#     "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
#     "pk": "pk_%(table_name)s",
# })
Base = declarative_base(cls=CaseInsensitiveBase)
Base.metadata.reflect(bind=engine)
tables = Base.metadata.tables


class TableModel(Base):
    __table__ = tables['table_name']

    pass


# class Table1(Base):
#     __table__ = tables['no_pk_table']
#     """表没有主键，映射类时设置主键"""
#     __mapper_args__ = {
#         'primary_key': [__table__.columns.column_name, ]
#     }


@as_declarative()
class BaseTable(object):
    # 通用的字段
    id: Any
    create_time = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="创建时间")
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    is_delete = Column(Integer, default=0, comment="逻辑删除:0=未删除,1=删除")
    __name__: str

    @declared_attr
    def __tablename__(cls):
        return cls.__name__


class TableDemo(BaseTable):
    __tablename__ = "table_demo"
    id = Column(Integer, primary_key=True, index=True)


def test_query():
    table_model = TableModel()

    # result = session.query(distinct(TableModel.column_name)).filter(TableModel.column_pk == 1).all()
    # result = session.query(distinct(TableModel.column_name)).filter_by(column_pk=1).all()

    # df = pd.DataFrame(data=result, columns=)
    pass


def test_filter_update():
    # session.query()

    pass


def test_inspect():
    query_result = session.query(TableModel).all()
    table_model = session.query(TableModel).first()
    table_class = TableModel

    # DeclarativeMeta转dict
    table_mapper = inspect(table_class)
    column_list = [str(column.name) for column in table_mapper.columns]
    record_dict = {column_name: getattr(table_model, column_name) for column_name in column_list}
    logger.info(record_dict)
    # for table_model in query_result:
    #     record_dict = {column_name: getattr(table_model, column_name) for column_name in column_list}
    #     logger.info(record_dict)
    #     pass


def test01():
    table_model = TableModel()

    query: Query = session.query(TableModel)
    # data = query.filter(TableModel.column_name.notin_(['chench2'])).all()

    data = query.all()
    update_list = list()
    for item in data:
        print(item.column_name)
        update_item = dict()
        # update_item['column_pk'] = item.column_pk
        update_item['column_name'] = item.column_name + '1'
        update_list.append(update_item)

    session.bulk_insert_mappings(TableModel, update_list)
    # session.bulk_update_mappings(TableModel, update_list)
    session.commit()


if __name__ == '__main__':
    test_query()
    # test_inspect()
    pass
